﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[ManufacturerUrlFromSchemaXml]
(
	-- Add the parameters for the function here
	@schemaXml xml
)
RETURNS varchar(max)
AS
BEGIN
	declare @url varchar(max);
	set @url=@schemaXml.query('/schema/deviceDescription[position()<=1]/*[local-name()="root"]/*[local-name()="device"]/*[local-name()="manufacturerURL"]').value('.', 'varchar(max)')

	-- unify
	set @url=rtrim(lower(@url));

	if (charindex('http://', @url) <= 0)
		set @url=('http://'+@url)

	if charindex('/',reverse(@url))=1
		set @url=left(@url, len(@url)-1);

	return @url;
END
